package cn.fintecher.gis.postgis;

import cn.fintecher.gis.config.PostGISDataStoreConfig;
import cn.fintecher.gis.entity.MetadataConfig;
import cn.fintecher.gis.entity.QMetadataConfig;
import cn.fintecher.gis.model.BoundBox;
import cn.fintecher.gis.model.MapSpotData;
import cn.fintecher.gis.model.ParsingGeoJsonResults;
import cn.fintecher.gis.model.ParsingShpResults;
import cn.fintecher.gis.respository.MetadataConfigRepository;
import cn.fintecher.gis.service.UploadFileService;
import cn.fintecher.gis.utils.ShpFileFilter;
import cn.fintecher.gis.utils.Snowflake;
import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;
import org.geotools.data.*;
import org.geotools.data.shapefile.ShapefileDataStore;
import org.geotools.data.simple.SimpleFeatureCollection;
import org.geotools.data.simple.SimpleFeatureIterator;
import org.geotools.data.simple.SimpleFeatureSource;
import org.geotools.factory.CommonFactoryFinder;
import org.geotools.feature.FeatureCollection;
import org.geotools.feature.FeatureIterator;
import org.geotools.feature.simple.SimpleFeatureTypeBuilder;
import org.geotools.filter.text.cql2.CQLException;
import org.geotools.filter.text.ecql.ECQL;
import org.geotools.geojson.feature.FeatureJSON;
import org.geotools.geometry.jts.JTS;
import org.geotools.geometry.jts.JTSFactoryFinder;
import org.geotools.geometry.jts.ReferencedEnvelope;
import org.geotools.measure.Measure;
import org.geotools.referencing.CRS;
import org.geotools.referencing.crs.DefaultGeographicCRS;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.geom.GeometryCollection;
import org.locationtech.jts.geom.GeometryFactory;
import org.locationtech.jts.geom.Point;
import org.locationtech.jts.io.ParseException;
import org.locationtech.jts.io.WKTReader;
import org.opengis.feature.Feature;
import org.opengis.feature.Property;
import org.opengis.feature.simple.SimpleFeature;
import org.opengis.feature.simple.SimpleFeatureType;
import org.opengis.feature.type.AttributeDescriptor;
import org.opengis.feature.type.PropertyDescriptor;
import org.opengis.filter.Filter;
import org.opengis.filter.FilterFactory;
import org.opengis.geometry.BoundingBox;
import org.opengis.referencing.FactoryException;
import org.opengis.referencing.crs.CoordinateReferenceSystem;
import org.opengis.referencing.operation.MathTransform;
import org.opengis.referencing.operation.TransformException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import si.uom.SI;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Query;
import java.io.File;
import java.io.IOException;
import java.io.StringWriter;
import java.nio.charset.Charset;
import java.util.*;

/**
 * Created by ChenChang on 2018/12/7.
 */
@Service
public class PostGISService {
    private final Logger log = LoggerFactory.getLogger(PostGISService.class);

    @Autowired
    private UploadFileService uploadFileService;
    @Autowired
    private MetadataConfigRepository metadataConfigRepository;
    @Autowired
    private Snowflake snowflake;
    @Autowired
    private EntityManagerFactory emf;
    @Autowired
    private PostGISDataStoreConfig dataStoreConfig;

    static void setAttrMap(Map<String, Class> mapFields, List<Map> fields, List<AttributeDescriptor> attrList) {
        for (int i = 0; i < attrList.size(); i++) {
            AttributeDescriptor attr = attrList.get(i);
            String name = attr.getName().toString();
            Class type = attr.getType().getBinding();
            String _name = name;
            if (mapFields.containsKey(name)) {
                _name = _name + "_1";
            }
            if (!StringUtils.equals(name, "the_geom")) {
                mapFields.put(_name, type);
                Map map = new HashMap();
                map.put("fieldShp", name);
                map.put("fieldNew", _name);
                fields.add(map);
            }
        }
    }

    public static void main(String[] args) throws ParseException, FactoryException, TransformException {
        WKTReader reader = new WKTReader(JTSFactoryFinder.getGeometryFactory());


        Geometry geometry = reader.read("POLYGON ((109.56378541495789 36.71243882129745,109.74447334228242 36.72872879291957,109.76374931138328 36.676655384516316,109.69064317770211 36.626086048519014,109.59493210621808 36.62690137894628,109.56378541495789 36.71243882129745))");
        System.err.println(geometry.getArea());
        Point centroid = geometry.getCentroid();
        String autoCode = "AUTO:42001," + centroid.getX() + "," + centroid.getY();
        CoordinateReferenceSystem auto = CRS.decode(autoCode);
        MathTransform transform = CRS.findMathTransform(DefaultGeographicCRS.WGS84, auto);

        Geometry projed = JTS.transform(geometry, transform);
        System.err.println(projed.getArea());
        Measure measure = new Measure(projed.getArea(), SI.SQUARE_METRE);
        System.err.println(measure.doubleValue());
    }

    /**
     * 计算地块面积
     *
     * @param table
     * @param areaCol
     * @param geomCol
     */
    public void updateArea(String table, String areaCol, String geomCol) {
        String deleteSql = "delete from  \"" + table + "\"  where  st_area(Geography(\"" + geomCol + "\")) < 1;";
        String updateSql = "update \"" + table + "\"  set  \"" + areaCol + "\"=st_area(Geography(\"" + geomCol + "\"));";

        try {
            executeUpdate(deleteSql);
            executeUpdate(updateSql);
            return;
        } catch (Exception e) {
            e.printStackTrace();
        }
        //delete
        //from "6536479884181118976"   where st_area(Geography( the_geom)) < 5
        throw new RuntimeException("处理重新计算面积出错");
    }

    public Boolean checkIntersects(String table1, String table2) {
        //TODO 这里可以使用PostGIS来进行相交计算 快速
//        "SELECT layer_1.fid  fid\n" +
//                ", st_union(ST_INTERSECTION(layer_1.the_geom, layer_2.the_geom)),layer_1.*,layer_2.*\n" +
//                "FROM \"6533155701317849088\" as layer_1, \"6533168188520026112\" as layer_2\n" +
//                "WHERE ST_Intersects(layer_1.the_geom, layer_2.the_geom)\n" +
//                "GROUP BY layer_1.fid,layer_2.fid;";
//

//        "SELECT layer_1.fid \n" +
//                ", ST_COLLECT(ST_INTERSECTION(layer_1.the_geom, layer_2.the_geom)) as the_geom\n" +
//                "FROM \"6533155701317849088\" as layer_1, \"6533168188520026112\" as layer_2 \n" +
//                "WHERE ST_Intersects(layer_1.the_geom, layer_2.the_geom)\n" +
//                "GROUP BY layer_1.fid,layer_2.fid;"
        //这有个可行的图层相交计算 还挺快

//        SELECT
//        row_number() over() as fid,
//        ST_Intersection(a.the_geom,  b.the_geom) as the_geom,
//        ST_Area(Geography(ST_Intersection(a.the_geom, b.the_geom))) as "结果形状面积",
//        ST_Area(Geography(b.the_geom)) as "对比图层总面积",
//        b.*
//        FROM "6544813790909616128" a, "6544894464335294464" b
//        WHERE ST_Intersects(a.the_geom, b.the_geom)
//        and b."用地代码" ='B11'


//        CREATE TABLE lgdtemp(
//                ID  SERIAL PRIMARY KEY,
//                state  INT  NOT NULL,
//                geom geometry(Polygon,4326)
//        );

        return null;
    }

    /**
     * 转换CRS到84坐标系
     */
    public String tranWktCrsToWgs84(String wkt, String sourceCrs) {
        EntityManager em = emf.createEntityManager();
        String sql = "SELECT ST_AsText(ST_Transform(st_geometryfromtext('" + wkt + "'," + sourceCrs + "),4326));";
        String targetWkt = "";
        try {
            em.getTransaction().begin();
            Query q = em.createNativeQuery(sql);
            List list = q.getResultList();
            targetWkt = (String) list.get(0);
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            em.close();
        }
        return targetWkt;
    }

    /**
     * @param wkt
     * @param sourceCrs
     * @param targetCrs
     * @return
     */
    public String tranWktCrs(String wkt, String sourceCrs, String targetCrs) {
        EntityManager em = emf.createEntityManager();
        String sql = "SELECT ST_AsText(ST_Transform(st_geometryfromtext('" + wkt + "'," + sourceCrs + ")," + targetCrs + "));";
        String targetWkt = "";
        try {
            em.getTransaction().begin();
            Query q = em.createNativeQuery(sql);
            List list = q.getResultList();
            targetWkt = (String) list.get(0);
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            em.close();
        }
        return targetWkt;
    }

    /**
     * 转换CRS到84坐标系
     *
     * @param table
     * @param sourceCrs
     * @param geomCol
     */
    public void transCrs(String table, String sourceCrs, String geomCol) {
        EntityManager em = emf.createEntityManager();
        String queryGeomType = "SELECT ST_GeometryType(" + geomCol + ") from  \"" + table + "\";";
        String type = "Geometry";
        try {
            em.getTransaction().begin();
            Query q = em.createNativeQuery(queryGeomType);
            List list = q.getResultList();
            type = (String) list.get(0);
            type = StringUtils.remove(type, "ST_");
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            em.close();
        }


        em = emf.createEntityManager();

        List<String> sqlList = new ArrayList<>();
        String updateSRid = "UPDATE \"" + table + "\" SET the_geom=ST_SetSRID(the_geom," + sourceCrs + ") ;";
        String createTempCol = "ALTER TABLE \"" + table + "\" ADD COLUMN the_geom_mercator geometry(" + type + ",4326);";
        String updateTempCol = "UPDATE  \"" + table + "\" SET the_geom_mercator = ST_Transform(" + geomCol + ", 4326) FROM spatial_ref_sys WHERE ST_SRID(" + geomCol + ") = srid;";
        String changeGeom = "ALTER TABLE \"" + table + "\" ALTER COLUMN " + geomCol + " TYPE geometry(Geometry, 4326)   USING ST_Transform(ST_SetSRID(" + geomCol + ", " + sourceCrs + "), 4326);";
        String updateGeom = "update \"" + table + "\" set " + geomCol + " =the_geom_mercator;";
        String removeTempCol = "ALTER TABLE  \"" + table + "\"   DROP COLUMN \"the_geom_mercator\";";
        sqlList.add(updateSRid);
        sqlList.add(createTempCol);
        sqlList.add(updateTempCol);
        sqlList.add(changeGeom);
        sqlList.add(updateGeom);
        sqlList.add(removeTempCol);
        try {


            for (int i = 0; i < sqlList.size(); i++) {
                em.getTransaction().begin();
                Query query = em.createNativeQuery(sqlList.get(i));
                query.executeUpdate();
                em.getTransaction().commit();
            }
            return;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            em.close();
        }
        throw new RuntimeException("处理转换WGS84出错");
    }

    /**
     * 根据用途判断shppath的有效性
     *
     * @param shpPath
     * @param forRead
     * @return
     */
    private boolean validateShp(String shpPath, boolean forRead) {
        File file = new File(shpPath);
        String ends = shpPath.substring(shpPath.lastIndexOf('.'), shpPath.length());
        File parent = new File(shpPath.substring(0, shpPath.lastIndexOf(File.separatorChar)));
        if (!parent.exists()) {
            parent.mkdirs();
        }
        return ".shp".equals(ends.toLowerCase()) && (forRead == file.exists());
    }

    /**
     * shp文件导入到postgis
     *
     * @param shpPath   shp文件路径，包括文件.shp的拓展名
     * @param tableName 存储到postgis中的表名称
     * @return
     * @throws IOException
     */
    public ParsingShpResults importShp(String shpPath, String tableName) throws IOException {
        DataStore dataStore = DataStoreFinder.getDataStore(dataStoreConfig.dataStoreParams());
        if (!validateShp(shpPath, true)) {
            return new ParsingShpResults(false, "postgis.shp.error", null);
        }
        ShapefileDataStore shapefileDataStore = new ShapefileDataStore(new File(shpPath).toURI().toURL());
        shapefileDataStore.setCharset(Charset.forName("GBK"));
        FeatureSource featureSource = shapefileDataStore.getFeatureSource();
        FeatureCollection featureCollection = featureSource.getFeatures();
        SimpleFeatureType simpleFeatureType = shapefileDataStore.getSchema();
        SimpleFeatureTypeBuilder typeBuilder = new SimpleFeatureTypeBuilder();
        typeBuilder.init(simpleFeatureType);
        typeBuilder.setName(tableName);
        SimpleFeatureType simpleFeatureType1 = typeBuilder.buildFeatureType();
        dataStore.createSchema(simpleFeatureType1);
        String crs = "4326";
        try {
            crs = String.valueOf(CRS.lookupEpsgCode(simpleFeatureType1.getCoordinateReferenceSystem(), true));
        } catch (FactoryException e) {
            e.printStackTrace();
        }
        FeatureIterator iterator = featureCollection.features();
        FeatureWriter<SimpleFeatureType, SimpleFeature> featureWriter = dataStore.getFeatureWriterAppend(tableName, Transaction.AUTO_COMMIT);
        Map<String, Object> fields = new HashMap<>();
        Collection<PropertyDescriptor> collection = simpleFeatureType.getDescriptors();
        Iterator<PropertyDescriptor> propertyDescriptorIterator = collection.iterator();
        while (propertyDescriptorIterator.hasNext()) {
            PropertyDescriptor descriptor = propertyDescriptorIterator.next();
            fields.put(descriptor.getName().toString(), descriptor.getType().getBinding().getSimpleName());
        }

        while (iterator.hasNext()) {
            Feature feature = iterator.next();
            SimpleFeature simpleFeature = featureWriter.next();
            Collection<Property> properties = feature.getProperties();
            Iterator<Property> propertyIterator = properties.iterator();
            while (propertyIterator.hasNext()) {
                Property property = propertyIterator.next();
                simpleFeature.setAttribute(property.getName().toString(), property.getValue());
            }
            featureWriter.write();
        }
        iterator.close();
        featureWriter.close();
        shapefileDataStore.dispose();
        dataStore.dispose();
        return new ParsingShpResults(true, null, crs);
    }

    /**
     * 上传了Shp文件的处理
     *
     * @param fileId
     * @return
     */
    public ParsingShpResults uploadShp(String fileId) throws IOException {
        String shpPath = uploadFileService.unZipShpFile(fileId);
        File directory = new File(shpPath);
        File[] files = directory.listFiles(new ShpFileFilter());
        if (Objects.isNull(files) || files.length == 0) {
            return new ParsingShpResults(false, "postgis.noshp", null);
        }
        File shpFile = files[0];
        ParsingShpResults result = importShp(shpFile.getPath(), fileId);
        //这里处理了所有的数据都保存为WGS84坐标的了
        if (result.getIsSuccess()) {
            String transSource = result.getCrs();
            transCrs(fileId, transSource, "the_geom");
        }
        return result;

    }

    /**
     * 通过图层查询所有属性
     *
     * @param layer
     * @return
     * @throws IOException
     */
    public List<MetadataConfig> queryLayerAttribute(String layer) throws IOException {
        DataStore dataStore = DataStoreFinder.getDataStore(dataStoreConfig.dataStoreParams());
        QMetadataConfig qMetadataConfig = QMetadataConfig.metadataConfig;
        List<String> names = new ArrayList<>();
        SimpleFeatureSource featureSource = dataStore.getFeatureSource(layer);
        List<AttributeDescriptor> attributeDescriptorList = featureSource.getFeatures().getSchema().getAttributeDescriptors();
        for (AttributeDescriptor attributeDescriptor : attributeDescriptorList) {
            if (!StringUtils.equals(attributeDescriptor.getName().toString(), "the_geom")) {
                names.add(attributeDescriptor.getName().toString());
            }
        }
        List<MetadataConfig> metadataConfigs = Lists.newArrayList(metadataConfigRepository.findAll(qMetadataConfig.code.in(names)));
        dataStore.dispose();
        return metadataConfigs;
    }

    /**
     * 通过cql获取查询FeatureCollection集合
     *
     * @param cql
     * @param table
     * @return
     * @throws IOException
     * @throws CQLException
     */
    public SimpleFeatureCollection queryMap(DataStore dataStore, String cql, String table) throws IOException, CQLException {

        SimpleFeatureSource featureSource = dataStore.getFeatureSource(table);
        SimpleFeatureCollection features = featureSource.getFeatures();

        if (StringUtils.isBlank(cql)) {
            return features;
        }
        Filter filter = ECQL.toFilter(cql); // filterStr形式 如  name='武汉大学' or code like 'tt123%'
        return features.subCollection(filter);
    }

    /**
     * 通过cql语句查询图斑信息
     *
     * @param cql
     * @param table
     * @return
     * @throws IOException
     * @throws FactoryException
     * @throws CQLException
     */
    public List<MapSpotData> queryMapSpot(String cql, String table) throws IOException, CQLException {
        DataStore dataStore = DataStoreFinder.getDataStore(dataStoreConfig.dataStoreParams());
        List<MapSpotData> mapSpotData = queryMapSpot(dataStore, cql, table);
        dataStore.dispose();
        return mapSpotData;
    }

    public List<MapSpotData> queryMapSpot(DataStore dataStore, String cql, String table) throws IOException, CQLException {

        List<MapSpotData> list = new ArrayList<>();


        SimpleFeatureIterator iterator = queryMap(dataStore, cql, table).features();
        try {
            while (iterator.hasNext()) {
                SimpleFeature feature = iterator.next();
                MapSpotData mapSpotData = new MapSpotData();
                mapSpotData.setIdentifier(feature.getIdentifier().toString());
                Map<String, Object> map = new HashMap<>();
                for (Property attr : feature.getProperties()) {
                    map.put(attr.getName().toString(), attr.getValue());

                }
                map.remove("geom");
                map.remove("the_geom");
                BoundBox boundBox = new BoundBox();
                boundBox.setCrs(feature.getBounds().getCoordinateReferenceSystem().getName().getCode());
                boundBox.setMaxX(feature.getBounds().getMaxX());
                boundBox.setMaxY(feature.getBounds().getMaxY());
                boundBox.setMinX(feature.getBounds().getMinX());
                boundBox.setMinY(feature.getBounds().getMinY());
                mapSpotData.setBoundBox(boundBox);
                mapSpotData.setAttr(map);
                list.add(mapSpotData);
            }
        } finally {
            iterator.close();
        }
        return list;

    }

    public void query(String qs, String table, String attr) throws IOException, FactoryException {
        DataStore dataStore = DataStoreFinder.getDataStore(dataStoreConfig.dataStoreParams());
        SimpleFeatureSource featureSource = dataStore.getFeatureSource(table);
        CoordinateReferenceSystem sourceCRS = CRS.decode("EPSG:4326");

        final FilterFactory ff = CommonFactoryFinder.getFilterFactory();

        //

        Filter filter =
                ff.equal(ff.property(attr), ff.literal(qs), false);
        CoordinateReferenceSystem crs2 = DefaultGeographicCRS.WGS84;

        SimpleFeatureCollection features = featureSource.getFeatures();

        features.accepts(feature -> {

            if (filter.evaluate(feature)) {


                Object dlmc = ((SimpleFeature) feature).getAttribute("DLMC");
                BoundingBox boundingBox = feature.getBounds();

                CoordinateReferenceSystem targetCRS = DefaultGeographicCRS.WGS84;
                ReferencedEnvelope re = new ReferencedEnvelope(boundingBox.getMinX(), boundingBox.getMaxX(), boundingBox.getMinY(), boundingBox.getMaxY(), sourceCRS);
                try {
                    BoundingBox b = re.toBounds(targetCRS);
                    log.debug("maxX:{},maxY:{},minX:{},minY:{}", b.getMaxX(), b.getMaxY(), b.getMinX(), b.getMinY());
//                    System.err.println(b.getMaxX());
                } catch (TransformException e) {
                    e.printStackTrace();
                }

//                try {
//                    BoundingBox box84=feature.getBounds().toBounds(DefaultGeographicCRS.WGS84);
//                    System.err.println(box84.getMaxX());
//                } catch (TransformException e) {
//                    e.printStackTrace();
//                }
//                log.debug("maxX:{},maxY:{},minX:{},minY:{}",feature.getBounds().getMaxX(),feature.getBounds().getMaxY(),feature.getBounds().getMinX(),feature.getBounds().getMinY());

//                feature.getBounds().getCoordinateReferenceSystem().getCoordinateSystem().toString();
                System.out.println(feature.getIdentifier()

                        + " is DLMC: " + dlmc);

            }

        }, null);

        dataStore.dispose();
    }

    /**
     * wkt字符串转换为 geometry
     *
     * @param wkt
     * @return
     * @throws ParseException
     */
    public Geometry getGeomByWkt(String wkt) throws ParseException {
        WKTReader reader = new WKTReader(JTSFactoryFinder.getGeometryFactory());


        Geometry geometry = reader.read(wkt);
//        Point centroid = mpolygon.getCentroid();
//        String autoCode = "AUTO:42001," + centroid.getX() + "," + centroid.getY();
//        CoordinateReferenceSystem auto = CRS.decode(autoCode);
//        MathTransform transform = CRS.findMathTransform(DefaultGeographicCRS.WGS84, auto);
//        MultiPolygon projed = (MultiPolygon) JTS.transform(mpolygon, transform);
//        MathTransform transform = CRS.findMathTransform(DefaultGeographicCRS.WGS84, DefaultGeographicCRS.WGS84);
//
//        MultiPolygon projed = (MultiPolygon) JTS.transform(mpolygon, transform);
//
//        Polygon p = (Polygon) feature.getDefaultGeometry();

//        Measure<Double, Area> measure = Measure.valueOf(geometry.getArea(), SI.SQUARE_METRE);
//        System.out.println(measure);
        return geometry;

    }

    public ParsingShpResults intersectionWkt(String wkt, String table, String cql) throws Exception {
        DataStore dataStore = DataStoreFinder.getDataStore(dataStoreConfig.dataStoreParams());
        Geometry geometry = getGeomByWkt(wkt);
        SimpleFeatureCollection featureCollection = queryMap(dataStore, cql, table);
        Double sumArea = getGeometryArea(geometry);
        /**
         * mapFields记录的是两个图层的属性名称，
         * fields2为图层2的字段
         */
        Map<String, Class> mapFields = new HashMap();
        List<Map> fields2 = new ArrayList();
        SimpleFeatureType featureType = featureCollection.getSchema();
        String crs = featureType.getCoordinateReferenceSystem().getCoordinateSystem().getName().toString();
        List<AttributeDescriptor> attrList = featureType.getAttributeDescriptors();
        setAttrMap(mapFields, fields2, attrList);
        String newTableName = String.valueOf(snowflake.next());
        //定义图形信息和属性信息
        SimpleFeatureTypeBuilder tb = new SimpleFeatureTypeBuilder();
        tb.setCRS(featureType.getCoordinateReferenceSystem());
        tb.setName(newTableName);
        tb.add("the_geom", GeometryCollection.class);
        tb.add("结果形状面积", Double.class);
        tb.add("对比图层总面积", Double.class);
        for (String key : mapFields.keySet()) {
            tb.add(key, mapFields.get(key));
        }
        //设置编码
        SimpleFeatureType sf = tb.buildFeatureType();
        dataStore.createSchema(sf);
        FeatureWriter<SimpleFeatureType, SimpleFeature> writer = dataStore.getFeatureWriterAppend(tb.getName(), Transaction.AUTO_COMMIT);
        //设置Writer
        //开始计算
        SimpleFeatureIterator iterator = featureCollection.features();
        while (iterator.hasNext()) {
            SimpleFeature feature2 = iterator.next();
            Geometry geom2 = (Geometry) feature2.getDefaultGeometry();
            Geometry geomOut;
            Geometry out1;
            try {
                out1 = geometry.intersection(geom2);
            } catch (Exception e) {
                continue;
            }

            if (out1.isEmpty()) {
                continue;
            }
//            if (out1 instanceof Polygon && "Polygon".equals(out1.getGeometryType())) {
//                Polygon[] polygons = {(Polygon) out1};
//                MultiPolygon multiPolygon = new MultiPolygon(polygons, out1.getFactory());
//                geomOut = multiPolygon;
//            } else {
//                geomOut = out1.getFactory().createGeometryCollection();
//            }
            List<Geometry> list = new ArrayList<>();
            list.add(out1);
            geomOut = new GeometryCollection(GeometryFactory.toGeometryArray(list), out1.getFactory());
            SimpleFeature featureOut = writer.next();
            featureOut.setAttribute("the_geom", geomOut);
            featureOut.setAttribute("结果形状面积", out1.getArea());
            featureOut.setAttribute("对比图层总面积", sumArea);
            for (int i = 0; i < fields2.size(); i++) {
                Map map = fields2.get(i);
                String fieldShp = map.get("fieldShp").toString(),
                        fieldNew = map.get("fieldNew").toString();
                featureOut.setAttribute(fieldNew, feature2.getAttribute(fieldShp));
            }
            writer.write();
        }
        writer.close();
        updateArea(newTableName, "结果形状面积", "the_geom");
        dataStore.dispose();
        return new ParsingShpResults(true, newTableName, crs);
    }

    public ParsingShpResults intersectionLayer(String table1, String table2) throws Exception {
        return intersectionLayer(table1, table2, null);
    }

    private Map<String, String> queryColomnList(String table) {
        Map<String, String> map = new HashMap<>();
        EntityManager em = emf.createEntityManager();
        String sql = "select c.\"column_name\" from information_schema.columns c where c.\"table_name\" ='" + table + "';";
        List<String> list = new ArrayList<>();
        try {
            em.getTransaction().begin();
            Query q = em.createNativeQuery(sql);
            list = q.getResultList();
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            em.close();
        }
        for (String col : list) {
            map.put(col, col);
        }
        map.remove("fid");
        map.remove("the_geom");
        map.remove("结果形状面积");
        map.remove("对比图层总面积");
        map.remove("SHAPE_Leng");
        map.remove("SHAPE_Area");
        map.remove("SHAPE_Area");
        map.remove("OBJECTID");
        return map;

    }

    private String createSelectIntersectionSql(String table1, String table2, String cql) {

        Map<String, String> table1AttrMap = queryColomnList(table1);
        Map<String, String> table2AttrMap = queryColomnList(table2);
        for (String key : table1AttrMap.keySet()) {
            if (table2AttrMap.containsKey(key)) {
                table2AttrMap.remove(key);
                Long id=snowflake.next();
                table2AttrMap.put(key, key + "_"+id.toString());
            }
        }
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT ROW_NUMBER\t() OVER () AS fid,");
        sb.append("ST_Intersection ( ST_CollectionExtract(A.the_geom,3), ST_CollectionExtract(b.the_geom,3) ) AS the_geom,");
        sb.append("ST_Area (\tGeography (\tST_Intersection ( ST_CollectionExtract(A.the_geom,3), ST_CollectionExtract(b.the_geom,3) ))) AS \"结果形状面积\",");

        for (String key : table1AttrMap.keySet()) {
            sb.append("A.\"" + key + "\" as \"" + table1AttrMap.get(key) + "\",");
        }

        for (String key : table2AttrMap.keySet()) {
            sb.append("b.\"" + key + "\" as \"" + table2AttrMap.get(key) + "\",");
        }
        sb.append("\tST_Area (\tGeography ( ST_CollectionExtract(b.the_geom,3))) AS \"对比图层总面积\" ");
        sb.append("FROM");
        sb.append(" \"" + table1 + "\" A,");
        sb.append(" \"" + table2 + "\" b ");
        sb.append(" WHERE ST_Intersects (ST_CollectionExtract(A.the_geom,3), ST_CollectionExtract(b.the_geom,3)) ");
        String newCql = cql;
        if (StringUtils.isNotBlank(cql)) {
            for (String key : table2AttrMap.keySet()) {
                if(cql.contains(key)){
                    String[] cqls = cql.split("\\s+");
                    for(String col : cqls){
                        String str= col.replace("\"", "");
                        if(str.equals(key)){
                            newCql = newCql.replace(col,"b."+col);
                        }
                    }
                }
            }
            sb.append(" AND " + newCql);
        }
        return sb.toString();
    }

    public ParsingShpResults intersectionLayer(String table1, String table2, String cql) {
        String selectSql = createSelectIntersectionSql(table1, table2, cql);
        String newTableName = String.valueOf(snowflake.next());
        String createSql = "create table \"" + newTableName + "\" as " + selectSql;

        EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            System.out.println("执行sql:"+cql);
            System.out.println("执行sql:"+createSql);
            Query query = em.createNativeQuery(createSql);
            query.executeUpdate();
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            return new ParsingShpResults(false, newTableName, null);
        } finally {
            em.close();
        }

        return new ParsingShpResults(true, newTableName, null);
    }

    public String getLayerGeoJson(String layerCode) throws IOException {
        DataStore dataStore = DataStoreFinder.getDataStore(dataStoreConfig.dataStoreParams());
        FeatureSource featureSource = dataStore.getFeatureSource(layerCode);
        FeatureCollection featureCollection = featureSource.getFeatures();
        FeatureJSON featureJSON = new FeatureJSON();
        StringWriter stringWriter = new StringWriter();
        featureJSON.writeFeatureCollection(featureCollection, stringWriter);
        String geoJson = stringWriter.toString();
        dataStore.dispose();
        return geoJson;
    }

    public void removeSchema(String table) throws IOException {
        DataStore dataStore = DataStoreFinder.getDataStore(dataStoreConfig.dataStoreParams());
        dataStore.removeSchema(table);
        dataStore.dispose();
//        String dropTable = "DROP TABLE \"" + table + "\";";
//        try {
//            executeUpdate(dropTable);
//            return;
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        throw new RuntimeException("处理重新计算面积出错");
    }

    private void executeUpdate(String sql) {
        EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            Query query = em.createNativeQuery(sql);
            query.executeUpdate();
            em.getTransaction().commit();
            return;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            em.close();
        }
        throw new RuntimeException("处理重新计算面积出错");
    }

    public ParsingGeoJsonResults intersectionLayerGeoJson(String table1, String table2) throws Exception {
        DataStore dataStore = DataStoreFinder.getDataStore(dataStoreConfig.dataStoreParams());
        ParsingShpResults result = intersectionLayer(table1, table2);
        if (!result.getIsSuccess()) {
            return new ParsingGeoJsonResults(false, null);
        }
        String tableName = result.getMessage();
        String geoJson = getLayerGeoJson(tableName);
        dataStore.removeSchema(tableName);
        dataStore.dispose();
        return new ParsingGeoJsonResults(true, geoJson);

    }

    private Double getGeometryArea(Geometry geometry) throws FactoryException, TransformException {
        Point centroid = geometry.getCentroid();
        String autoCode = "AUTO:42001," + centroid.getX() + "," + centroid.getY();
        CoordinateReferenceSystem auto = CRS.decode(autoCode);
        MathTransform transform = CRS.findMathTransform(DefaultGeographicCRS.WGS84, auto);
        Geometry projed = JTS.transform(geometry, transform);
        Measure measure = new Measure(projed.getArea(), SI.SQUARE_METRE);
        return measure.doubleValue();
    }
}
